
/****** Object:  Table [dbo].[Activity]    Script Date: 3/23/2014 3:18:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Activity')
    drop table Activity;
GO

CREATE TABLE [dbo].[Activity](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[game_id] [int] NULL,
	[user_id] [int] NULL,
	[ip] [varchar](255) NULL,
	[date] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[Category]    Script Date: 3/23/2014 3:18:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Category')
    drop table Category;
GO

CREATE TABLE [dbo].[Category](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[category] [varchar](255) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[CatGame]    Script Date: 3/23/2014 3:18:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'CatGame')
    drop table CatGame;
GO

CREATE TABLE [dbo].[CatGame](
	[category_id] [int] NULL,
	[game_id] [int] NULL
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Game]    Script Date: 3/23/2014 3:18:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Game')
    drop table Game;
GO

CREATE TABLE [dbo].[Game](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[category] [varchar](255) NULL,
	[title] [varchar](255) NULL,
	[latin_title] [varchar](255) NULL,
	[swf] [varchar](255) NULL,
	[thumbnail] [varchar](255) NULL,
	[large_img] [varchar](255) NULL,
	[added] [datetime] NULL,
	[active] [bit] NULL,
	[plays_total] [bigint] NULL,
	[plays_today] [int] NULL,
	[keywords] [varchar](1024) NULL,
	[description] [varchar](1024) NULL,
	[controls] [varchar](1024) NULL,
	[height] [int] NULL,
	[width] [int] NULL,
	[votes] [int] NULL,
	[votes_value] [int] NULL,
	[rating] [int] NULL,
	[featured] [bit] NULL,
	[game_tag] [varchar](255) NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Game] ADD  CONSTRAINT [DF_Game_added]  DEFAULT (getdate()) FOR [added]
GO

ALTER TABLE [dbo].[Game] ADD  CONSTRAINT [DF_Game_active]  DEFAULT ((1)) FOR [active]
GO

ALTER TABLE [dbo].[Game] ADD  CONSTRAINT [DF_Game_plays_total]  DEFAULT ((0)) FOR [plays_total]
GO

ALTER TABLE [dbo].[Game] ADD  CONSTRAINT [DF_Game_plays_today]  DEFAULT ((0)) FOR [plays_today]
GO

ALTER TABLE [dbo].[Game] ADD  CONSTRAINT [DF_Game_featured]  DEFAULT ((0)) FOR [featured]
GO

SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[Users]    Script Date: 3/23/2014 3:18:26 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Users')
    drop table Users;
GO

CREATE TABLE [dbo].[Users](
	[uname] [varchar](15) NOT NULL,
	[Pwd] [varchar](25) NOT NULL,
	[userRole] [varchar](25) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddGame]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddGame]
GO
CREATE PROCEDURE [dbo].[AddGame]
   @cat varchar(255),
   @title varchar(255),
  -- @latin_title varchar(255),
   @swf varchar(255),
   @thumb varchar(255),
   @large_img varchar(255) = '',
   @key varchar(1024),
   @desc varchar(1024),
   @cont varchar(1024),
   @height int = 480,
   @width int = 640,
   @game_tag varchar(255)
AS
	declare @latin_title varchar(255)
	set @latin_title = REPLACE(REPLACE(REPLACE(REPLACE(@title, ' ', '_'),':','_'),',','_'),'''','_')
   
   -- find out if this is a duplicate by comparing latin_title
   declare @game_id int
   set @game_id = NULL
   --select @game_id = id from Game g where g.game_tag = @game_tag
   select @game_id = id from Game g where g.latin_title = @latin_title
   if @game_id IS NULL
   BEGIN

      -- insert the game
      Insert Game (category, title, latin_title, swf, thumbnail, large_img, keywords, [description], controls, height, width, game_tag)
      values (@cat, @title, @latin_title, @swf, @thumb, @large_img, @key, @desc, @cont, @height, @width, @game_tag)
   END
ELSE
BEGIN
return @game_id
END
RETURN 0
GO

/****** Object:  StoredProcedure [dbo].[PlayGame]    Script Date: 3/23/2014 3:18:15 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PlayGame]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PlayGame]
GO

CREATE PROCEDURE [dbo].[PlayGame]
	@latin_title varchar(255)
AS
	declare @game_id int
	set @game_id = NULL
	select @game_id = id from Game where latin_title = @latin_title
	if @game_id IS NOT NULL
	BEGIN
		update Game set
			plays_today = plays_today + 1,
			plays_total = plays_total + 1
			where id = @game_id;
		Select * from Game g
		where g.id = @game_id
	END
RETURN 


GO


INSERT INTO Users values('admin','Qwerty123','Admin')